-- **************************************************************************
-- ** 创建者   : suning
-- ** 创建日期 : 20220816
-- ** 功能描述 ：城市时效汇总
-- **************************************************************************
-- **************************** 修改日志 ************************************
-- **************************************************************************
-- ** 依赖表：
-- jms_dm.dm_fastest_route_area_effective   --城市时效
-- jms_dm.dm_route_whole_base_dt            --静态路由
-- jms_dm.dm_traffic_flow_network_dt        --流量流向
-- **************************************************************************
-- *******************  CURRENT_VERSION ： V.1.1  ***************************
-- **************************************************************************
drop table jms_dm.dm_route_city_summary_dt;
create external table if not exists jms_dm.dm_route_city_summary_dt (
    in_from_regional_code   string           comment'始发大区编码'
   ,in_from_regional_desc   string           comment'始发大区名称'
   ,out_to_regional_code    string           comment'目的大区编码'
   ,out_to_regional_desc    string           comment'目的大区名称'
   ,in_from_agent_code      string           comment'始发代理区编码'
   ,in_from_agent_desc      string           comment'始发代理区名称'
   ,out_to_agent_code       string           comment'目的代理区编码'
   ,out_to_agent_desc       string           comment'目的代理区名称'
   ,city_cnt                bigint           comment'代理区城市流向线路数量'
   ,city_equal_cnt          bigint           comment'代理区城市流向持平线路数量'
   ,equal_cnt               decimal(15,2)    comment'持平件量'
   ,slow_cnt                decimal(15,2)    comment'慢于件量'
   ,direct_cnt              decimal(15,2)    comment'直发件量'
   ,trans_cnt               decimal(15,2)    comment'中转件量'
   ,all_cnt                 decimal(15,2)    comment'总件量'
   ,equal_line_cnt          bigint           comment'持平线路数'
   ,slow_line_cnt           bigint           comment'慢于线路数'
   ,direct_line_cnt         bigint           comment'直发线路数'
   ,trans_line_cnt          bigint           comment'中转线路数'
   ,all_line_cnt            bigint           comment'总线路数'
   ,all_city_cnt            bigint           comment'全国流向'
   ,all_city_equal_cnt      bigint           comment'全国持平流向'
   ,all_equal_piect_cnt     decimal(15,2)    comment'全国持平件量'
   ,all_slow_piect_cnt      decimal(15,2)    comment'全国慢于件量'
   ,all_direct_piect_cnt    decimal(15,2)    comment'全国直发件量'
   ,all_trans_piect_cnt     decimal(15,2)    comment'全国中转件量'
   ,all_piect_cnt           decimal(15,2)    comment'全国件量  '
   ,all_equal_line_cnt      bigint           comment'全国持平线路数'
   ,all_slow_line_cnt       bigint           comment'全国慢于线路数'
   ,all_direct_line_cnt     bigint           comment'全国直发线路数'
   ,all_trans_line_cnt      bigint           comment'全国中转线路数'
   ,all_any_line_cnt        bigint           comment'全国总线路数'
)comment '规划平台-静态路由-城市时效汇总'
partitioned by (dt string comment '分区日期')
stored as parquet
location '/dw/hive/jms_dm.db/external/dm_route_city_summary_dt'
tblproperties (
'discover.partitions'='false',
'parquet.column.index.access'='true'
);



drop table jms_tmp.dm_route_city_summary_dt_tmp
create external table bidefault.dm_route_city_summary_dt_tmp(
     in_from_agent_code       string          comment'始发代理区编码'
    ,in_from_agent_desc       string          comment'始发代理区名称'
    ,in_from_new_agent_code   string          comment'新始发代理区编码'
    ,in_from_new_agent_desc   string          comment'新始发代理区名称'
    ,in_from_regional_code    string          comment'始发大区编码'
    ,in_from_regional_desc    string          comment'始发大区名称'
    ,out_to_agent_code        string          comment'目的代理区编码'
    ,out_to_agent_desc        string          comment'目的代理区名称'
    ,out_to_new_agent_code    string          comment'新目的代理区编码'
    ,out_to_new_agent_desc    string          comment'新目的代理区名称'
    ,out_to_regional_code     string          comment'目的大区编码'
    ,out_to_regional_desc     string          comment'目的大区名称'
    ,economic_name            string          comment'经济圈名称'
    ,city_cnt                 bigint          comment'代理区城市流向线路数量'
    ,city_equal_cnt           bigint          comment'代理区城市流向持平线路数量'
    ,equal_cnt                decimal(15,2)   comment'持平件量'
    ,slow_cnt                 decimal(15,2)   comment'慢于件量'
    ,direct_cnt               decimal(15,2)   comment'直发件量'
    ,trans_cnt                decimal(15,2)   comment'中转件量'
    ,all_cnt                  decimal(15,2)   comment'总件量 '
    ,equal_line_cnt           bigint          comment'持平线路数'
    ,slow_line_cnt            bigint          comment'慢于线路数'
    ,direct_line_cnt          bigint          comment'直发线路数'
    ,trans_line_cnt           bigint          comment'中转线路数'
    ,all_line_cnt             bigint          comment'总线路数'
    ,all_city_cnt             bigint          comment'全国流向'
    ,all_city_equal_cnt       bigint          comment'全国持平流向'
    ,all_equal_piect_cnt      decimal(15,2)   comment'全国持平件量'
    ,all_slow_piect_cnt       decimal(15,2)   comment'全国慢于件量'
    ,all_direct_piect_cnt     decimal(15,2)   comment'全国直发件量'
    ,all_trans_piect_cnt      decimal(15,2)   comment'全国中转件量'
    ,all_piect_cnt            decimal(15,2)   comment'全国件量'
    ,all_equal_line_cnt       bigint          comment'全国持平线路数'
    ,all_slow_line_cnt        bigint          comment'全国慢于线路数'
    ,all_direct_line_cnt      bigint          comment'全国直发线路数'
    ,all_trans_line_cnt       bigint          comment'全国中转线路数'
    ,all_any_line_cnt         bigint          comment'全国总线路数'
) comment '规划平台-静态路由-城市时效汇总中间表'
partitioned by (dt string comment '分区日期')
stored as parquet
location '/dw/hive/jms_tmp.db/external/dm_route_city_summary_dt_tmp'
tblproperties (
'discover.partitions'='false',
'parquet.column.index.access'='true'
);
